********************************************************************************
** 	TITLE:		UK05_results                                                  ** 
**	AUTHOR:	    Philippe Mongrain                                             **
**	DATA:       1918-2019election_results_by_pcon         		              **
**	VERSION:	Stata 16					                                  **
**	DATE:		October 2022  				                                  **
********************************************************************************

* Version control

version 16.0

* Open log file

capture log close
log using "UK05_results.smcl", replace

* Import the dataset

import excel "1918-2019election_results_by_pcon.xlsx", cellrange(B5:AN650) sheet(2005) clear

* Reshape

drop H K N Q T W Z AC AF AI AL

rename B id
rename C constituency_name
rename D county
rename E region
rename F country
rename G electorate
rename I votes1
rename J share1
rename L votes2
rename M share2
rename O votes3
rename P share3
rename R votes4
rename S share4
rename U votes5
rename V share5
rename X votes6
rename Y share6
rename AA votes7
rename AB share7
rename AD votes8
rename AE share8
rename AG votes9
rename AH share9
rename AJ votes10
rename AK share10
rename AM totalvotes
rename AN turnout

reshape long votes share, i(id) j(party_code)  

gen party_name = " "

replace party_name = "Conservative" if party_code == 1 
replace party_name = "Liberal Democrat" if party_code == 2
replace party_name = "Labour" if party_code == 3
replace party_name = "UK Independence Party" if party_code == 4
replace party_name = "Green" if party_code == 5 
replace party_name = "SNP" if party_code == 6
replace party_name = "Plaid Cymru" if party_code == 7
replace party_name = "DUP" if party_code == 8
replace party_name = "Sinn Fein" if party_code == 9
replace party_name = "SDLP" if party_code == 10
replace party_name = "UUP" if party_code == 11
replace party_name = "Other" if party_code == 12

drop party_code

gen party_code = .

replace party_code = 1 if party_name == "Conservative"
replace party_code = 2 if party_name == "Labour"
replace party_code = 3 if party_name == "Liberal Democrat"
replace party_code = 4 if party_name == "SNP"
replace party_code = 5 if party_name == "Plaid Cymru"
replace party_code = 6 if party_name == "UK Independence Party"
replace party_code = 7 if party_name == "Green"
replace party_code = -9999 if country == "Northern Ireland"
replace party_code = 9 if party_code > 7
 
* Generate rank of parties

gsort constituency_name -votes
bysort constituency_name : gen rank = _n

gen first = party_name if rank == 1
gen second = party_name if rank == 2
gen third = party_name if rank == 3

bysort constituency_name : gen winner05 = first[1]
bysort constituency_name : gen runnerup05 = second[2]
bysort constituency_name : gen thirdplace05 = third[3]

* Generate margins of victory

bysort constituency_name : gen margin = (share[1] - share[2])*100

* Generate effective number of electoral parties

gen sqrvotepr = (share)^2

bysort constituency_name : egen sumsqrvotepr = sum(sqrvotepr)

gen enep = 1/sumsqrvotepr

* Labelling parties

replace party_name = "1" if party_name == "Conservative"
replace party_name = "2" if party_name == "Labour"
replace party_name = "3" if party_name == "Liberal Democrat"
replace party_name = "4" if party_name == "Scottish National Party"
replace party_name = "5" if party_name == "Plaid Cymru"
replace party_name = "6" if party_name == "UK Independence Party"
replace party_name = "7" if party_name == "Green"
replace party_name = "9" if party_name!="1" & party_name!="2" & party_name!="3" & party_name!="4" & party_name!="5" & party_name!="6" & party_name!="7"

destring (party_name), replace

replace winner05 = "1" if winner05 == "Conservative"
replace winner05 = "2" if winner05 == "Labour"
replace winner05 = "3" if winner05 == "Liberal Democrat"
replace winner05 = "4" if winner05 == "Scottish National Party"
replace winner05 = "5" if winner05 == "Plaid Cymru"
replace winner05 = "6" if winner05 == "UK Independence Party"
replace winner05 = "7" if winner05 == "Green"
replace winner05 = "9" if winner05!="1" & winner05!="2" & winner05!="3" & winner05!="4" & winner05!="5" & winner05!="6" & winner05!="7"

destring (winner05), replace

replace runnerup05 = "1" if runnerup05 == "Conservative"
replace runnerup05 = "2" if runnerup05 == "Labour"
replace runnerup05 = "3" if runnerup05 == "Liberal Democrat"
replace runnerup05 = "4" if runnerup05 == "Scottish National Party"
replace runnerup05 = "5" if runnerup05 == "Plaid Cymru"
replace runnerup05 = "6" if runnerup05 == "UK Independence Party"
replace runnerup05 = "7" if runnerup05 == "Green"
replace runnerup05 = "9" if runnerup05!="1" & runnerup05!="2" & runnerup05!="3" & runnerup05!="4" & runnerup05!="5" & runnerup05!="6" & runnerup05!="7"

destring (runnerup05), replace

replace thirdplace05 = "1" if thirdplace05 == "Conservative"
replace thirdplace05 = "2" if thirdplace05 == "Labour"
replace thirdplace05 = "3" if thirdplace05 == "Liberal Democrat"
replace thirdplace05 = "4" if thirdplace05 == "Scottish National Party"
replace thirdplace05 = "5" if thirdplace05 == "Plaid Cymru"
replace thirdplace05 = "6" if thirdplace05 == "UK Independence Party"
replace thirdplace05 = "7" if thirdplace05 == "Green"
replace thirdplace05 = "9" if thirdplace05!="1" & thirdplace05!="2" & thirdplace05!="3" & thirdplace05!="4" & thirdplace05!="5" & thirdplace05!="6" & thirdplace05!="7"

destring (thirdplace05), replace

label define party 1 "Conservative" 2 "Labour" 3 "Liberal Democrat" 4 "SNP" 5 "Plaid Cymru" 6 "UK Independence Party" 7 "Green" 9 "Other"
label values party_name winner05 runnerup05 thirdplace05 party
 
* Selecting, ordering, and sorting variables

keep id constituency_name party_name rank votes share margin enep winner05 runnerup05 thirdplace05

order id constituency_name party_name rank votes share margin enep winner05 runnerup05 thirdplace05

sort id rank

drop if rank > 3

* Drop duplicates

duplicates tag id, gen(dup)
duplicates drop id, force
drop dup

* Change constituency ID

replace id = subinstr(id,"E1400","1",.)
replace id = subinstr(id,"S1400","2",.)
replace id = subinstr(id,"W0700","3",.)
replace id = subinstr(id,"N0600","4",.)

* Rename some constituencies for 2010 BES

replace constituency_name = "ABERDEENSHIRE WEST & KINCARDINE" if constituency_name == "WEST ABERDEENSHIRE & KINCARDINE"
replace constituency_name = "ALDRIDGE - BROWNHILLS" if constituency_name == "ALDRIDGE-BROWNHILLS"
replace constituency_name = "AYRSHIRE CENTRAL" if constituency_name == "CENTRAL AYRSHIRE"
replace constituency_name = "BEDFORDSHIRE NORTH EAST" if constituency_name == "NORTH EAST BEDFORDSHIRE"
replace constituency_name = "CORNWALL NORTH" if constituency_name == "NORTH CORNWALL"
replace constituency_name = "DORSET WEST" if constituency_name == "WEST DORSET"
replace constituency_name = "DUNBARTONSHIRE EAST" if constituency_name == "EAST DUNBARTONSHIRE"
replace constituency_name = "DUNBARTONSHIRE WEST" if constituency_name == "WEST DUNBARTONSHIRE"
replace constituency_name = "EDINBURGH NORTH AND LEITH" if constituency_name == "NORTH EDINBURGH AND LEITH"
replace constituency_name = "HERTFORDSHIRE NORTH EAST" if constituency_name == "NORTH EAST HERTFORDSHIRE"
replace constituency_name = "LANARCK & HAMILTON EAST" if constituency_name == "LANARK & HAMILTON EAST"
replace constituency_name = "LIVINGSTONE" if constituency_name == "LIVINGSTON"
replace constituency_name = "NORFOLK NORTH WEST" if constituency_name == "NORTH WEST NORFOLK"
replace constituency_name = "NORFOLK SOUTH WEST" if constituency_name == "SOUTH WEST NORFOLK"
replace constituency_name = "SOUTHWARK NORTH & BERMONDSEY" if constituency_name == "NORTH SOUTHWARK & BERMONDSEY"
replace constituency_name = "STRATFORD ON AVON" if constituency_name == "STRATFORD-ON-AVON"
replace constituency_name = "SURREY SOUTH WEST" if constituency_name == "SOUTH WEST SURREY"
replace constituency_name = "SWINDON NORTH" if constituency_name == "NORTH SWINDON"
replace constituency_name = "YNYS-MON" if constituency_name == "YNYS MÔN"

* Create election variable

gen election05 = "2005 British general election"

* Saving file

save UK05_results, replace

log close